package com.x.crm.dao;



import com.x.crm.model.Customer;
import com.x.crm.model.CustomerShop;
import com.x.crm.model.Intention;
import com.x.crm.util.Constant;
import com.x.framework.dao.BaseDao;
import org.apache.commons.lang.StringUtils;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

/**
 * Created by Administrator on 2018/2/2.
 */
@Repository
public class CustomerShopDao extends BaseDao {
	private final static String baseSelectCustomerShopSql = "SELECT T1.*, T2.CUSTOMER_HEAD_IMAGE , T2.CUSTOMER_NICKNAME , T2.CUSTOMER_NAME, T2.CUSTOMER_PHONE,T2.CUSTOMER_GENDER, T2.CUSTOMER_CREATE_TIME, T2.PROVINCE_ID,T2.CITY_ID, T2.PROVINCE_NAME, T2.CITY_NAME FROM  CRM_CUSTOMER_SHOP T1,CRM_CUSTOMER T2 "+
			"WHERE  T1.CUSTOMER_ID = T2.CUSTOMER_ID ";
	public List<CustomerShop> selectCustomerShopList(CustomerShop customerShop) throws Exception {
		StringBuilder sql = new StringBuilder(baseSelectCustomerShopSql);
		List<Object> params = new ArrayList<>();
		searchCondition(customerShop, sql, params);
		if (this.isNotNull(customerShop.getOrderColumn())){
			sql.append("ORDER BY ").append(customerShop.getOrderColumn()).append(" ");
		} else {
			sql.append("ORDER BY T2.CUSTOMER_CREATE_TIME ");
		}
		if (this.isNotNull(customerShop.getOrderMode())){
			sql.append(customerShop.getOrderMode());
		}else{
			sql.append("DESC ");
		}
		return this.queryForList(sql.toString(), CustomerShop.class, params, customerShop);
	}

	//通过来源查询 或者通过跟进人查询 跟进状态和 预约状态
	private final static String baseFollowInfoSelectCustomerShopSql = "SELECT COUNT(T1.CUSTOMER_SHOP_ID) AS CUSTOMER_NUM, T1.CHANNEL_NAME, T1.FOLLOWER_NAME  FROM  CRM_CUSTOMER_SHOP T1,CRM_CUSTOMER T2 "+
			"WHERE  T1.CUSTOMER_ID = T2.CUSTOMER_ID AND T1.CHANNEL_NAME IS NOT NULL AND T1.FOLLOWER_NAME  IS NOT NULL  ";

	public List<Map<String, Object>> selectFollowUpCustomerShopList(CustomerShop customerShop, String searchMode) throws Exception {
		StringBuilder sql = new StringBuilder(baseFollowInfoSelectCustomerShopSql);
		List<Object> params = new ArrayList<>();
		searchCondition(customerShop, sql, params);
		if (this.isNotNull(searchMode)){
			sql.append("GROUP BY T1.").append(searchMode).append(" ");
		}
		sql.append("ORDER BY T2.CUSTOMER_CREATE_TIME ");
		RowMapper<Map<String, Object>> mapper = new RowMapper<Map<String, Object>>() {
			@Override
			public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
				Map<String, Object> map = new HashMap<String, Object>();
				map.put("channelName", rs.getString("CHANNEL_NAME"));
				map.put("followerName", rs.getString("FOLLOWER_NAME"));
				map.put("customerNum", rs.getInt("CUSTOMER_NUM"));
				return map;
			}
		};
		return this.queryForList(sql.toString(), mapper, params);
	}

	/**
	 * 日统计跟进 预约 明细
	 */
	private final static String baseSelectFollowUpShopListSql = "SELECT T1.*, T2.CUSTOMER_HEAD_IMAGE , T2.CUSTOMER_NICKNAME , T2.CUSTOMER_NAME, T2.CUSTOMER_PHONE,T2.CUSTOMER_GENDER, T2.CUSTOMER_CREATE_TIME, T2.PROVINCE_ID,T2.CITY_ID, T2.PROVINCE_NAME, T2.CITY_NAME FROM  CRM_CUSTOMER_SHOP T1,CRM_CUSTOMER T2 "+
			"WHERE  T1.CUSTOMER_ID = T2.CUSTOMER_ID AND T1.CHANNEL_NAME IS NOT NULL AND T1.FOLLOWER_NAME IS NOT NULL ";
	public List<CustomerShop> selectFollowUpCustomerShopInfoList(CustomerShop customerShop) throws Exception {
		StringBuilder sql = new StringBuilder(baseSelectFollowUpShopListSql);
		List<Object> params = new ArrayList<>();
		searchCondition(customerShop, sql, params);
		return this.queryForList(sql.toString(), CustomerShop.class, params, customerShop);
	}


    //根据时间查询跟进状态
	private final static String baseSelectFollowUpNumSql = "SELECT COUNT(T1.CUSTOMER_SHOP_ID) AS CUSTOMER_NUM  FROM  CRM_CUSTOMER_SHOP T1 "+
			"WHERE T1.CHANNEL_NAME IS NOT NULL AND T1.FOLLOWER_NAME IS NOT NULL AND T1.FOLLOW_STATE = 10 ";

	public Map<String, Object> selectFollowUpNum(CustomerShop customerShop, Date queryDate) throws Exception {
		StringBuilder sql = new StringBuilder(baseSelectFollowUpNumSql);
		List<Object> params = new ArrayList<>();
		if(queryDate != null) {
			sql.append("AND T1.NEXT_FOLLOW_DATETIME = ? ");
			params.add(queryDate);
		}
		searchCondition(customerShop, sql, params);
		RowMapper<Map<String, Object>> mapper = new RowMapper<Map<String, Object>>() {
			@Override
			public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
				Map<String, Object> map = new HashMap<String, Object>();
				map.put("followUpNum", rs.getInt("CUSTOMER_NUM"));
				return map;
			}
		};
		return this.queryForList(sql.toString(), mapper, params).get(0);
	}

	//根据时间查询预约状态
	private final static String baseSelectOrderUpNumSql = "SELECT COUNT(T1.CUSTOMER_SHOP_ID) AS CUSTOMER_NUM  FROM  CRM_CUSTOMER_SHOP T1 "+
			"WHERE T1.CHANNEL_NAME IS NOT NULL AND T1.FOLLOWER_NAME IS NOT NULL AND T1.FOLLOW_STATE = 20 ";

	public Map<String, Object> selectOrderUpNum(CustomerShop customerShop, Date queryDate) throws Exception {
		StringBuilder sql = new StringBuilder(baseSelectOrderUpNumSql);
		List<Object> params = new ArrayList<>();
		if(queryDate != null) {
			sql.append("AND T1.APPOINTMENT_TIME = ? ");
			params.add(queryDate);
		}
		searchCondition(customerShop, sql, params);
		RowMapper<Map<String, Object>> mapper = new RowMapper<Map<String, Object>>() {
			@Override
			public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
				Map<String, Object> map = new HashMap<String, Object>();
				map.put("orderUpNum", rs.getInt("CUSTOMER_NUM"));
				return map;
			}
		};
		return this.queryForList(sql.toString(), mapper, params).get(0);
	}



	//根据时间查询成交状态数量
	private final static String baseSelectFixtureNumSql = "SELECT COUNT(DISTINCT(T1.CUSTOMER_SHOP_ID)) AS CUSTOMER_NUM  FROM CRM_CUSTOMER_SHOP T1, CRM_INTENTION T2 "+
			"WHERE T1.CUSTOMER_SHOP_ID = T2.CUSTOMER_SHOP_ID AND T1.CHANNEL_NAME IS NOT NULL AND T1.FOLLOWER_NAME IS NOT NULL AND T1.FOLLOW_STATE = 50 ";

	public Map<String, Object> selectFixtureNum(CustomerShop customerShop, Date queryDate) throws Exception {
		StringBuilder sql = new StringBuilder(baseSelectFixtureNumSql);
		List<Object> params = new ArrayList<>();
		if(queryDate != null) {
			sql.append("AND T2.FIXTURE_TIME = ? ");
			params.add(queryDate);
		}
		searchCondition(customerShop, sql, params);
		RowMapper<Map<String, Object>> mapper = new RowMapper<Map<String, Object>>() {
			@Override
			public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
				Map<String, Object> map = new HashMap<String, Object>();
				map.put("fixtureUpNum", rs.getInt("CUSTOMER_NUM"));
				return map;
			}
		};
		return this.queryForList(sql.toString(), mapper, params).get(0);
	}


	// 通过来源或者跟进人查询成交状态
	private final static String baseSelectFixtureCustomerShopSql = "SELECT COUNT(DISTINCT(T1.CUSTOMER_SHOP_ID)) AS CUSTOMER_NUM, T1.CHANNEL_NAME, T1.FOLLOWER_NAME FROM  CRM_CUSTOMER_SHOP T1, CRM_INTENTION T2 "+
			"WHERE T1.CUSTOMER_SHOP_ID = T2.CUSTOMER_SHOP_ID AND T1.CHANNEL_NAME IS NOT NULL AND T1.FOLLOWER_NAME IS NOT NULL ";

	public List<Map<String, Object>> selectFixtureCustomerShopList(CustomerShop customerShop, Intention intention, String searchMode) throws Exception {
		StringBuilder sql = new StringBuilder(baseSelectFixtureCustomerShopSql);
		List<Object> params = new ArrayList<>();
//		if (this.isNotNull(intention.getFixtureTime())) {
//			sql.append("AND T2.FIXTURE_TIME = ? ");
//			params.add(intention.getFixtureTime());
//		}
		if (this.isNotNull(customerShop.getFollowState())) {
			sql.append("AND T1.FOLLOW_STATE = ? ");
			params.add(customerShop.getFollowState());
		}
		if (this.isNotNull(customerShop.getShopId())) {
			sql.append("AND T1.SHOP_ID = ? ");
			params.add(customerShop.getShopId());
		}
		if (this.isNotNull(searchMode)){
			sql.append("GROUP BY T1.").append(searchMode).append(" ");
		}
		RowMapper<Map<String, Object>> mapper = new RowMapper<Map<String, Object>>() {
			@Override
			public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
				Map<String, Object> map = new HashMap<String, Object>();
				map.put("channelName", rs.getString("CHANNEL_NAME"));
				map.put("followerName", rs.getString("FOLLOWER_NAME"));
				map.put("customerNum", rs.getInt("CUSTOMER_NUM"));
				return map;
			}
		};
		return this.queryForList(sql.toString(), mapper, params);
	}

    //成交状态
	private final static String baseSelectFixtureCustomerShopInfoSql = "SELECT T1.*, T2.CUSTOMER_HEAD_IMAGE , T2.CUSTOMER_NICKNAME , T2.CUSTOMER_NAME, T2.CUSTOMER_PHONE,T2.CUSTOMER_GENDER,T2.CUSTOMER_CREATE_TIME,T2.PROVINCE_ID,T2.CITY_ID, T2.PROVINCE_NAME, T2.CITY_NAME FROM CRM_CUSTOMER_SHOP T1,CRM_CUSTOMER T2,CRM_INTENTION T3 "+
			"WHERE  T1.CUSTOMER_ID = T2.CUSTOMER_ID AND T1.CUSTOMER_SHOP_ID = T3.CUSTOMER_SHOP_ID  AND T1.CHANNEL_NAME IS NOT NULL AND T1.FOLLOWER_NAME IS NOT NULL ";


	public List<CustomerShop> selectFixtureCustomerShopInfoList(CustomerShop customerShop, Intention intention) throws Exception {
		StringBuilder sql = new StringBuilder(baseSelectFixtureCustomerShopInfoSql);
		List<Object> params = new ArrayList<>();
//		if (this.isNotNull(intention.getFixtureTime())) {
//			sql.append("AND T3.FIXTURE_TIME = ? ");
//			params.add(intention.getFixtureTime());
//		}
		if (this.isNotNull(customerShop.getShopId())) {
			sql.append("AND T1.SHOP_ID = ? ");
			params.add(customerShop.getShopId());
		}
		if (this.isNotNull(customerShop.getFollowState())) {
			sql.append("AND T1.FOLLOW_STATE = ? ");
			params.add(customerShop.getFollowState());
		}
		sql.append("GROUP BY T3.CUSTOMER_SHOP_ID ");
		sql.append("ORDER BY T2.CUSTOMER_CREATE_TIME ");
		return this.queryForList(sql.toString(), CustomerShop.class, params, customerShop);
	}

	private final static String baseSelectCustomerShopCount = "SELECT COUNT(T1.CUSTOMER_SHOP_ID) FROM  CRM_CUSTOMER_SHOP T1, CRM_CUSTOMER T2 WHERE T1.CUSTOMER_ID = T2.CUSTOMER_ID AND T2.CUSTOMER_PHONE IS NOT NULL  ";

	public int selectCustomerShopNum(CustomerShop customerShop) throws Exception {
		StringBuilder sql = new StringBuilder(baseSelectCustomerShopCount);
		List<Object> params = new ArrayList<>();
		searchCondition(customerShop, sql, params);
		return this.queryForInt(sql.toString(), params);
	}



	public int  updateReleaseFollowInfo(CustomerShop customerShop) throws  Exception {
		return this.execute("UPDATE CRM_CUSTOMER_SHOP  SET FOLLOWER_NAME = NULL, FOLLOWER_ID = NULL , FOLLOW_STATE = ?  WHERE  CUSTOMER_SHOP_ID = ? ", Constant.CUSTOMER_NOT_FOLLOW, customerShop.getCustomerShopId() );
	}



	/**
	 * 查询客户基本信息以及其他信息
	 * 201807版
	 */
	private final static String baseSimpleCustomerShopSql = "SELECT T1.*,T2.CUSTOMER_PHONE , T2.CUSTOMER_HEAD_IMAGE , T2.CUSTOMER_NICKNAME , T2.CUSTOMER_GENDER  FROM  CRM_CUSTOMER_SHOP T1, CRM_CUSTOMER T2 WHERE T1.CUSTOMER_ID = T2.CUSTOMER_ID AND T2.CUSTOMER_PHONE IS NOT NULL  ";

	public List<CustomerShop> selectSimpleCustomerShopList(CustomerShop customerShop) throws Exception {
		StringBuilder sql = new StringBuilder(baseSimpleCustomerShopSql);
		List<Object> params = new ArrayList<>();
		searchSimpleCondition(customerShop, sql, params);
		return this.queryForList(sql.toString(), CustomerShop.class, params, customerShop);
	}

	private void searchSimpleCondition(CustomerShop customerShop, StringBuilder sql, List<Object> params) {
		if (StringUtils.isNotEmpty(customerShop.getShopId())) {
			sql.append("AND T1.SHOP_ID = ? ");
			params.add(customerShop.getShopId());
		}
		if (StringUtils.isNotEmpty(customerShop.getFollowerId())) {
			sql.append("AND T1.FOLLOWER_ID = ? ");
			params.add(customerShop.getFollowerId());
		}
		if (StringUtils.isNotEmpty(customerShop.getCustomerShopId())) {
			sql.append("AND T1.CUSTOMER_SHOP_ID = ? ");
			params.add(customerShop.getCustomerShopId());
		}
		Customer customer = customerShop.getCustomer();
		if (customer != null) {
			if (StringUtils.isNotEmpty(customer.getCustomerPhone())) {
				sql.append("AND T2.CUSTOMER_PHONE = ? ");
				params.add(customer.getCustomerPhone());
			}
		}
	}

	private void searchCondition(CustomerShop customerShop, StringBuilder sql, List<Object> params){
		if (this.isNotNull(customerShop.getCustomerShopId())) {
			sql.append("AND T1.CUSTOMER_SHOP_ID = ? ");
			params.add(customerShop.getCustomerShopId());
		}
		if (this.isNotNull(customerShop.getCustomerId())) {
			sql.append("AND T1.CUSTOMER_ID = ? ");
			params.add(customerShop.getCustomerId());
		}
		if (this.isNotNull(customerShop.getCustomer()) && this.isNotNull(customerShop.getCustomer().getCustomerPhone())){
			sql.append("AND T2.CUSTOMER_PHONE like ? ");
			params.add(customerShop.getCustomer().getCustomerPhone()+ "%");
		}
		if (this.isNotNull(customerShop.getCustomer()) && this.isNotNull(customerShop.getCustomer().getCustomerName())){
			sql.append("AND T2.CUSTOMER_NAME like ? ");
			params.add(customerShop.getCustomer().getCustomerName() + "%");
		}
		if (this.isNotNull(customerShop.getCustomerShopId())) {
			sql.append("AND T1.CUSTOMER_SHOP_ID = ? ");
			params.add(customerShop.getCustomerShopId());
		}
		if (this.isNotNull(customerShop.getFollowerId())) {
			sql.append("AND T1.FOLLOWER_ID = ? ");
			params.add(customerShop.getFollowerId());
		}
		if (this.isNotNull(customerShop.getShopId())) {
			sql.append("AND T1.SHOP_ID = ? ");
			params.add(customerShop.getShopId());
		}
		if (this.isNotNull(customerShop.getFollowState())) {
			sql.append("AND T1.FOLLOW_STATE = ? ");
			params.add(customerShop.getFollowState());
		}
		if (this.isNotNull(customerShop.getLevelId())) {
			sql.append("AND T1.LEVEL_ID = ? ");
			params.add(customerShop.getLevelId());
		}
		if (this.isNotNull(customerShop.getChannelId())) {
			sql.append("AND T1.CHANNEL_ID = ? ");
			params.add(customerShop.getChannelId());
		}
//		if (this.isNotNull(customerShop.getNextFollowDatetime())) {
//			sql.append("AND T1.NEXT_FOLLOW_DATETIME = ? ");
//			params.add(customerShop.getNextFollowDatetime());
//		}
//		if (this.isNotNull(customerShop.getAppointmentTime())) {
//			sql.append("AND T1.APPOINTMENT_TIME = ? ");
//			params.add(customerShop.getAppointmentTime());
//		}
//		if (this.isNotNull(customerShop.getCustomerShopMode())){
//			switch (customerShop.getCustomerShopMode()){
//				case "01":
//					sql.append("AND T1.FOLLOW_STATE >=10 AND T1.FOLLOW_STATE <=20 "); //跟进客户
//					break;
//				case "02":
//					sql.append("AND T1.FOLLOW_STATE > 20 "); //结束客户
//					break;
//				case "03":
//					sql.append("AND T1.NEXT_FOLLOW_DATETIME < ? AND T1.FOLLOW_STATE = 1 "); //过期未处理
//					params.add(getCurrentDate(0, "yyyy-MM-dd"));
//					break;
//				case "04":
//					sql.append("AND T1.NEXT_FOLLOW_DATETIME = ? AND T1.FOLLOW_STATE in (1,10) "); //今日待处理
//					params.add(getCurrentDate(0, "yyyy-MM-dd"));
//					break;
//			}
//		}
		if (this.isNotNull(customerShop.getReach())) {
			if (customerShop.getReach()){
				sql.append("AND T1.REACH_COUNT IS NOT NULL ");
			}else{
				sql.append("AND T1.REACH_COUNT IS  NULL ");
			}
		}
	}
}